﻿CREATE PROCEDURE [maint].[ConfigDbMaintenance]
@DbName [sysname], @FullBackupSchedule INT=NULL, @FullBackupTime INT=NULL, @DiffBackupSchedule INT=NULL, @DiffBackupTime INT=NULL, @DiffBackupFrequency INT=NULL, @BackupSets INT=NULL, @BackupAgent NCHAR (3)=NULL, @DefragThreshold INT=NULL, @DefragWeekSchedule INT=NULL, @DefragDaySchedule INT=NULL, @CheckDbWeekSchedule INT=NULL, @CheckDbDaySchedule INT=NULL, @MaintenanceWindow INT=NULL, @UpdateStatsSchedule INT=NULL, @StatsLatency INT=NULL
AS
BEGIN
	DECLARE @RC int, @ERROR int
	SET @RC = 0

	DELETE [config].[DbMaintenance]
	WHERE ([DbName] = @DbName);
		
	DECLARE @InsertStatement nvarchar(1024);	
	SET @InsertStatement = N'
	INSERT [config].[DbMaintenance](
		[ServerName]
		,[DbName]
		,[FullBackupSchedule]
		,[FullBackupTime]
		,[DiffBackupSchedule]
		,[DiffBackupTime]
		,[DiffBackupFrequency]
		,[BackupSets]
		,[BackupAgent]
		,[DefragThreshold]
		,[DefragWeekSchedule]
		,[DefragDaySchedule]
		,[CheckDbWeekSchedule]
		,[CheckDbDaySchedule]
		,[MaintenanceWindow]
		,[UpdateStatsDaySchedule]
		,[StatsLatency]
	)
	VALUES(
		@@SERVERNAME
		,''' + @DbName + '''
		,' + ISNULL(CAST(@FullBackupSchedule AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@FullBackupTime AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@DiffBackupSchedule AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@DiffBackupTime AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@DiffBackupFrequency AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@BackupSets AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(N' ''' + @BackupAgent + N''' ',N'DEFAULT') + N'
		,' + ISNULL(CAST(@DefragThreshold AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@DefragWeekSchedule AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@DefragDaySchedule AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@CheckDbWeekSchedule AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@CheckDbDaySchedule AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@MaintenanceWindow AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@UpdateStatsSchedule AS nvarchar),N'DEFAULT') + N'
		,' + ISNULL(CAST(@StatsLatency AS nvarchar),N'DEFAULT') + N'
	)';
	EXEC(@InsertStatement);
	
	SET @ERROR = @@ERROR
	IF(@ERROR <> 0)
		GOTO ERROR_HANDLER

	GOTO EXIT_PROC

ERROR_HANDLER:
	RAISERROR('Failed to insert configuration details for %s database',16,1,@DbName);
	IF(@RC = 0)
		SET @RC = @ERROR

EXIT_PROC:
	RETURN(@RC)
END